/*
MySQL Backup
Database: studentmanager
Backup Time: 2022-01-06 23:37:53
*/

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `studentmanager`.`s_admin`;
DROP TABLE IF EXISTS `studentmanager`.`s_attendance`;
DROP TABLE IF EXISTS `studentmanager`.`s_clazz`;
DROP TABLE IF EXISTS `studentmanager`.`s_course`;
DROP TABLE IF EXISTS `studentmanager`.`s_leave`;
DROP TABLE IF EXISTS `studentmanager`.`s_score`;
DROP TABLE IF EXISTS `studentmanager`.`s_selected_course`;
DROP TABLE IF EXISTS `studentmanager`.`s_student`;
DROP TABLE IF EXISTS `studentmanager`.`s_teacher`;
CREATE TABLE `s_admin` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `s_attendance` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `course_id` int(5) NOT NULL,
  `student_id` int(5) NOT NULL,
  `type` varchar(11) NOT NULL,
  `date` varchar(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `attendance_course_foreign_key` (`course_id`),
  KEY `attendace_student_foreign_key` (`student_id`),
  CONSTRAINT `attendace_student_foreign_key` FOREIGN KEY (`student_id`) REFERENCES `s_student` (`id`),
  CONSTRAINT `attendance_course_foreign_key` FOREIGN KEY (`course_id`) REFERENCES `s_course` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
CREATE TABLE `s_clazz` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `info` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
CREATE TABLE `s_course` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `teacher_id` int(5) NOT NULL,
  `course_date` varchar(32) DEFAULT NULL,
  `selected_num` int(5) NOT NULL DEFAULT '0',
  `max_num` int(5) NOT NULL DEFAULT '50',
  `info` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `course_teacher_foreign` (`teacher_id`),
  CONSTRAINT `course_teacher_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `s_teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
CREATE TABLE `s_leave` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `student_id` int(5) NOT NULL,
  `info` varchar(512) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `remark` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `leave_student_foreign_key` (`student_id`),
  CONSTRAINT `leave_student_foreign_key` FOREIGN KEY (`student_id`) REFERENCES `s_student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
CREATE TABLE `s_score` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `student_id` int(5) NOT NULL,
  `course_id` int(5) NOT NULL,
  `score` double(5,2) NOT NULL,
  `remark` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `selected_course_student_fk` (`student_id`),
  KEY `selected_course_course_fk` (`course_id`),
  CONSTRAINT `s_score_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `s_course` (`id`),
  CONSTRAINT `s_score_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `s_student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;
CREATE TABLE `s_selected_course` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `student_id` int(5) NOT NULL,
  `course_id` int(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `selected_course_student_fk` (`student_id`),
  KEY `selected_course_course_fk` (`course_id`),
  CONSTRAINT `selected_course_course_fk` FOREIGN KEY (`course_id`) REFERENCES `s_course` (`id`),
  CONSTRAINT `selected_course_student_fk` FOREIGN KEY (`student_id`) REFERENCES `s_student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;
CREATE TABLE `s_student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `sn` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `clazz_id` int(5) NOT NULL,
  `sex` varchar(5) NOT NULL DEFAULT '男',
  `mobile` varchar(12) DEFAULT NULL,
  `qq` varchar(18) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`sn`),
  KEY `student_clazz_id_foreign` (`clazz_id`),
  CONSTRAINT `student_clazz_id_foreign` FOREIGN KEY (`clazz_id`) REFERENCES `s_clazz` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
CREATE TABLE `s_teacher` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `sn` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `clazz_id` int(5) NOT NULL,
  `sex` varchar(5) NOT NULL DEFAULT '男',
  `mobile` varchar(12) DEFAULT NULL,
  `qq` varchar(18) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`sn`),
  KEY `student_clazz_id_foreign` (`clazz_id`),
  CONSTRAINT `s_teacher_ibfk_1` FOREIGN KEY (`clazz_id`) REFERENCES `s_clazz` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
BEGIN;
LOCK TABLES `studentmanager`.`s_admin` WRITE;
DELETE FROM `studentmanager`.`s_admin`;
INSERT INTO `studentmanager`.`s_admin` (`id`,`username`,`password`,`status`) VALUES (1, 'admin', '111111', 1);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_attendance` WRITE;
DELETE FROM `studentmanager`.`s_attendance`;
INSERT INTO `studentmanager`.`s_attendance` (`id`,`course_id`,`student_id`,`type`,`date`) VALUES (13, 1, 2, '上午', '2018-09-04'),(15, 2, 2, '上午', '2019-07-02'),(16, 1, 2, '下午', '2021-12-19');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_clazz` WRITE;
DELETE FROM `studentmanager`.`s_clazz`;
INSERT INTO `studentmanager`.`s_clazz` (`id`,`name`,`info`) VALUES (1, '软件一班', '软件工程专业。'),(4, '数学一班', '大学数学专业'),(5, '计算机科学与技术一班', '计算机专业'),(6, '数学二班', '2班'),(7, '软件二班', '喵');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_course` WRITE;
DELETE FROM `studentmanager`.`s_course`;
INSERT INTO `studentmanager`.`s_course` (`id`,`name`,`teacher_id`,`course_date`,`selected_num`,`max_num`,`info`) VALUES (1, '大学英语', 9, '周三上午8点', 49, 50, '英语。'),(2, '大学数学', 10, '周三上午10点', 2, 50, '数学。'),(3, '计算机基础', 11, '周三上午', 3, 50, '计算机课程。'),(4, '数据库', 18, '周日全天', 0, 50, '啊是的请问的');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_leave` WRITE;
DELETE FROM `studentmanager`.`s_leave`;
INSERT INTO `studentmanager`.`s_leave` (`id`,`student_id`,`info`,`status`,`remark`) VALUES (13, 2, '世界这么大，想去看看', 1, '同意，你很6'),(14, 10, '秒', 0, NULL),(15, 10, '大家说的看不见啊看', 0, NULL);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_score` WRITE;
DELETE FROM `studentmanager`.`s_score`;
INSERT INTO `studentmanager`.`s_score` (`id`,`student_id`,`course_id`,`score`,`remark`) VALUES (67, 4, 2, 78.00, '中等'),(68, 9, 1, 50.00, '不及格'),(69, 2, 3, 90.00, '大幅缩短'),(71, 13, 3, 58.00, '无'),(72, 14, 3, 96.00, '无'),(73, 15, 3, 72.00, '无'),(74, 16, 3, 73.00, '无'),(75, 17, 3, 89.00, '无'),(76, 18, 3, 57.00, '无'),(77, 19, 3, 57.00, '无'),(78, 20, 3, 59.00, '无'),(79, 21, 3, 66.00, '无'),(80, 22, 3, 77.00, '无'),(81, 23, 3, 91.00, '无'),(82, 24, 3, 98.00, '无'),(83, 25, 3, 64.00, '无'),(84, 26, 3, 63.00, '无'),(85, 27, 3, 85.00, '无'),(86, 28, 3, 69.00, '无'),(87, 29, 3, 99.00, '无'),(88, 30, 3, 56.00, '无');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_selected_course` WRITE;
DELETE FROM `studentmanager`.`s_selected_course`;
INSERT INTO `studentmanager`.`s_selected_course` (`id`,`student_id`,`course_id`) VALUES (18, 2, 1),(20, 2, 3),(21, 4, 3),(24, 9, 1),(71, 13, 3),(72, 14, 3),(73, 15, 3),(74, 16, 3),(75, 17, 3),(76, 18, 3),(77, 19, 3),(78, 20, 3),(79, 21, 3),(80, 22, 3),(81, 23, 3),(82, 24, 3),(83, 25, 3),(84, 26, 3),(85, 27, 3),(86, 28, 3),(87, 29, 3),(88, 30, 3);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_student` WRITE;
DELETE FROM `studentmanager`.`s_student`;
INSERT INTO `studentmanager`.`s_student` (`id`,`sn`,`username`,`password`,`clazz_id`,`sex`,`mobile`,`qq`,`photo`) VALUES (2, 'S51528202992845', '张三纷', '123456', 1, '女', '13545454548', '1332365656', NULL),(4, 'S51528379586807', '王麻子', '111111', 5, '男', '13356565656', '123456', NULL),(9, 'S41528633634989', '马冬梅', '1', 5, '男', '13333332133', '131313132323', 'bb12326f-ef6c-4d3d-a2ae-f9eb30a15ad4.jpg'),(10, 'S51639919813073', 'xhj', '123456', 1, '男', '13921598730', '1111111', 'da5b02ae-a470-43dc-b8e1-49a6f197c732.png'),(11, 'S51639920173444', '爱回收', '111', 5, '女', '13345684755', '1223451', '3ac8c78e-9bb9-497f-9dcc-973fb01d6fc7.gif'),(12, 'S11641461424862', '大幅波动', 'zdfvdz ', 1, '男', '13321651561', '4153453', '1409a64d-1799-40d6-bd40-fd11384d9faf.png'),(13, 'S11641461424863', 'xfg', 'zdfvdz ', 7, '男', '13321651561', '4153453', NULL),(14, 'S11641461424864', '针对', 'zdfvdz ', 7, '男', '13321651561', '4153453', NULL),(15, 'S11641461424865', '放不下', 'zdfvdz ', 4, '男', '13321651561', '4153453', NULL),(16, 'S11641461424866', 'fgnet', 'zdfvdz ', 5, '男', '13321651561', '4153453', NULL),(17, 'S11641461424867', 'zrg', 'zdfvdz ', 1, '男', '13321651561', '4153453', NULL),(18, 'S11641461424868', 'zdfh', 'zdfvdz ', 7, '男', '13321651561', '4153453', NULL),(19, 'S11641461424869', 'xngf', 'zdfvdz ', 7, '男', '13321651561', '4153453', NULL),(20, 'S11641461424870', 'sfshfb', 'zdfvdz ', 4, '男', '13321651561', '4153453', NULL),(21, 'S11641461424871', 'etw', 'zdfvdz ', 5, '男', '13321651561', '4153453', NULL),(22, 'S11641461424872', 'eq', 'zdfvdz ', 1, '男', '13321651561', '4153453', NULL),(23, 'S11641461424873', 'dfb', 'zdfvdz ', 7, '男', '13321651561', '4153453', NULL),(24, 'S11641461424874', 'srg', 'zdfvdz ', 6, '男', '13321651561', '4153453', NULL),(25, 'S11641461424875', 'bdf', 'zdfvdz ', 4, '男', '13321651561', '4153453', NULL),(26, 'S11641461424876', 'zdv', 'zdfvdz ', 5, '男', '13321651561', '4153453', NULL),(27, 'S11641461424877', 'aeg', 'zdfvdz ', 1, '男', '13321651561', '4153453', NULL),(28, 'S11641461424878', 'sfe', 'zdfvdz ', 7, '男', '13321651561', '4153453', NULL),(29, 'S11641461424879', 'vzc', 'zdfvdz ', 6, '男', '13321651561', '4153453', NULL),(30, 'S11641461424880', 'mgh', 'zdfvdz ', 4, '男', '13321651561', '4153453', NULL);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `studentmanager`.`s_teacher` WRITE;
DELETE FROM `studentmanager`.`s_teacher`;
INSERT INTO `studentmanager`.`s_teacher` (`id`,`sn`,`username`,`password`,`clazz_id`,`sex`,`mobile`,`qq`,`photo`) VALUES (9, 'T11528608730648', '张三', '111', 4, '男', '13918655656', '1193284480', NULL),(10, 'T11528609224588', '李四老师', '111', 4, '男', '13656565656', '123456', NULL),(11, 'T51528617262403', '李老师', '123456', 5, '男', '18989898989', '1456655565', NULL),(18, 'T11561727746515', '夏青松', '123456', 1, '女', '15174857845', '1745854125', '5d447b8b-ec54-4a8e-919a-453aa7b6d33b.jpg');
UNLOCK TABLES;
COMMIT;
